package com.suixun.util;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.commons.lang.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * excel工具类
 * 
 * @author Administrator
 *
 */
public class ExcelUtil {

	/**
	 * 校验excel表内容是否有重复数据
	 * 
	 * @param wb
	 * @param excelTemplate如需要检验SIM,ICCID；则excelTemplate={"SIM","ICCID"}
	 * @return
	 */
	public static String checkRepeat(HSSFWorkbook wb, String[] excelTemplate) {
		HSSFSheet sheet = wb.getSheetAt(0);
		if (sheet.getLastRowNum() == 0) {
			return "表内容为空";
		}
		Set<Object> set = new HashSet<>();
		List<Integer> indexList = new LinkedList<>();
		int rowSize = sheet.getLastRowNum() + 1;
		int colSize = sheet.getRow(0).getLastCellNum();
		for (int j = 0; j < rowSize; j++) {
			// 创建一个行对象
			HSSFRow row1 = sheet.getRow(j);
			for (int i = 0; i < colSize; i++) {
				HSSFCell cell = row1.getCell(i);
				if (cell == null) {
					continue;
				}
				row1.getCell(i).setCellType(CellType.STRING);
				if (j == 0) {
					String title = row1.getCell(i).getRichStringCellValue().toString();
					for (String str : excelTemplate) {
						if (str.equals(title)) {
							indexList.add(i);
						}
					}
				}
			}
			if (j == 0) {
				break;
			}
		}
		for (int k = 1; k < rowSize; k++) {
			HSSFRow row2 = sheet.getRow(k);
			for (int i = 0; i < colSize; i++) {
				int flag = 0;
				if (indexList.size() > 0) {
					for (Integer index : indexList) {
						if (i == index) {
							flag = 1;
						}
					}
				}
				if (flag == 0) {
					continue;
				}
				if (row2.getCell(i) == null || row2.getCell(i).getRichStringCellValue() == null
						|| "".equals(row2.getCell(i).getRichStringCellValue().toString().trim())) {
					continue;
				}
				boolean isTrue = set.add(row2.getCell(i).getRichStringCellValue());
				if (!isTrue) {
					return String.format("第%d行第%d列数据重复", k + 1, i + 1);
				}
			}
		}
		return "true";
	}

	/**
	 * 
	 * <p>
	 * Title: checkExcel
	 * </p>
	 * <p>
	 * 功能描述:检测excel列的合法性
	 * </p>
	 * 
	 * @param wb
	 *            excel
	 * @param title
	 *            检验表头是否匹配,不能为空
	 * @param isEmpty
	 *            是否判空
	 * @return
	 */
	public static String checkExcel(Workbook wb, String[] title, boolean isEmpty) {
		String[] factorys = { "13.56", "2.4", "赛风", "卫小宝" };
		String[] operations = { "移动", "中国电信", "中国联通" };
		Sheet sheet = wb.getSheetAt(0);
		if (sheet.getLastRowNum() == 0) {
			return "表内容为空";
		}
		// 获取表头个列名
		List<String> indexList = new ArrayList<>();
		Row titleRow = sheet.getRow(0);
		int count = 0;
		for (int i = 0; i < titleRow.getLastCellNum(); i++) {
			Cell cell = titleRow.getCell(i);
			titleRow.getCell(i).setCellType(CellType.STRING);
			if (cell.getRichStringCellValue() == null || StringUtil.isEmpty(cell.getRichStringCellValue().toString())) {
				count++;
			}
			for (int j = 0; j < title.length; j++) {
				if (titleRow.getCell(i).getRichStringCellValue() != null
						&& title[j].equals(titleRow.getCell(i).getRichStringCellValue().toString())) {
					indexList.add(title[j]);
				}
			}
		}
		if ((indexList.size() + count) != titleRow.getLastCellNum()) {
			return "表头名字不符";
		}
		for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {
			Row row = sheet.getRow(j);
			if (row == null) {
				continue;
			}
			if (isEmpty) {
				int nullCount = getNullCounts(indexList.size(), row, indexList.size());
				if (nullCount >= indexList.size()) {
					continue;
				}
				if (title.length > row.getPhysicalNumberOfCells()) {
					return String.format("第%d行有数据为空", j + 1);
				}
			}
			for (int i = 0; i < row.getLastCellNum(); i++) {
				Cell cell = row.getCell(i);
				if (j == 0) {
					break;
				} else {
					if (cell == null) {
						continue;
					}
					cell.setCellType(CellType.STRING);
					if (isEmpty) {
						if (cell.getRichStringCellValue() == null
								|| !StringUtil.isNotEmpty(cell.getRichStringCellValue().toString().trim())) {
							return String.format("第%d行第%d列为空", j + 1, i + 1);
						}
					}
					if (cell.getRichStringCellValue() == null
							|| !StringUtil.isNotEmpty(cell.getRichStringCellValue().toString().trim())) {
						continue;
					}
					for (int k = 0; k < indexList.size(); k++) {
						if (i == k) {
							// 检验手机号
							if (indexList.get(k).contains("手机号") || indexList.get(k).contains("SIM")) {
								if (!Utils.isTel(cell.getRichStringCellValue().toString().replaceAll(" ", ""))) {
									return String.format("第%d行第%d列格式不符", j + 1, i + 1);
								}
								break;
							}
							// 检验ICCID
							if (indexList.get(k).contains("ICCID") || indexList.get(k).contains("批号")) {
								if (!cell.getRichStringCellValue().toString().replaceAll(" ", "").matches("^[A-Z0-9-]+")) {
									return String.format("第%d行第%d列格式不符", j + 1, i + 1);
								}
								break;
							}
							// 校验运营商
							if (indexList.get(k).contains("运营商")) {
								int num = 0;
								for (String operation : operations) {
									if (cell.getRichStringCellValue().toString().contains(operation)) {
										num++;
									}
								}
								if (num == 0) {
									return String.format("第%d行第%d列格式不符", j + 1, i + 1);
								}
								break;
							}
							// 校验箱号
							if (indexList.get(k).contains("箱号")) {
								if (!cell.getRichStringCellValue().toString().replaceAll(" ", "").matches("^[0-9]+")) {
									return String.format("第%d行第%d列格式不符", j + 1, i + 1);
								}
								break;
							}
							// 校验IMEI
							if (indexList.get(k).contains("IMEI")) {
								if (!cell.getRichStringCellValue().toString().replaceAll(" ", "").matches("^[0-9]{14,15}")) {
									return String.format("第%d行第%d列格式不符", j + 1, i + 1);
								}
								break;
							}
							// 校验考勤卡号
							if (indexList.get(k).contains("考勤卡号")) {
								if (!cell.getRichStringCellValue().toString().replaceAll(" ", "").matches("^[0-9]{10}")) {
									return String.format("第%d行第%d列格式不符", j + 1, i + 1);
								}
								break;
							}
							// 校验厂商
							if (indexList.get(k).contains("生产商")) {
								int num = 0;
								for (String factory : factorys) {
									if (cell.getRichStringCellValue().toString().equals(factory)) {
										num++;
									}
								}
								if (num == 0) {
									return String.format("第%d行第%d列格式不符", j + 1, i + 1);
								}
								break;
							}
							// 校验学校
							/*if (indexList.get(k).contains("学校") || indexList.get(k).contains("班级")
									|| indexList.get(k).contains("教师名") || indexList.get(k).contains("设备套餐")
									|| indexList.get(k).contains("姓名") || indexList.get(k).contains("昵称")) {
								String str = cell.getRichStringCellValue().toString();
								str = str.replace(" ", "");
								if (!str.matches("^[\\u4e00-\\u9fa5\\(\\（\\）\\)0-9a-zA-Z]{0,}$")) {
									return String.format("第%d行第%d列格式不符", j + 1, i + 1);
								}
								break;
							}*/
							// 校验金额
							if (indexList.get(k).contains("价格")) {
								if (!cell.getRichStringCellValue().toString().replaceAll(" ", "")
										.matches("^((([1-9]{1}\\d{0,9}))|([0]{1}))((\\.(\\d){2}))?$")) {
									return String.format("第%d行第%d列格式不符", j + 1, i + 1);
								}
								break;
							}
							// 校验试用期
							if (indexList.get(k).contains("试用期")) {
								if (!cell.getRichStringCellValue().toString().replaceAll(" ", "").matches("([0-9/])+")) {
									return String.format("第%d行第%d列格式不符", j + 1, i + 1);
								}
								break;
							}
						}
					}
				}
			}
		}
		return "true";
	}
	
	/**
	 * 
	 * <p>
	 * Title: zeroize
	 * </p>
	 * <p>
	 * 功能描述:自动补零工具
	 * </p>
	 * 
	 * @param str
	 *            需要补零的数字，如"1"
	 * @param number
	 *            需要补到多少位数,如number=3,则为"001"
	 * @return
	 */
	public static String zeroize(String str, int number) {
		if (str == null) {
			return null;
		}
		if (str.length() >= number) {
			return str;
		} else {
			int num = number - str.length();
			StringBuffer nStr = new StringBuffer();
			for (int i = 0; i < num; i++) {
				nStr.append("0");
			}
			nStr.append(str);
			return nStr.toString();
		}
	}

	/**
	 * 
	 * <p>
	 * Title: getExcelValue
	 * </p>
	 * <p>
	 * 功能描述: 获取将excel表中数据转换为List集合
	 * </p>
	 * 
	 * @param tableMap
	 *            excel将转换目标对象所保存的字段与表头Map,key表示表头对应的目标对象的属性,value表示表头名称,
	 *            如{"factory":"生产商","imei":"设备imei","fee":"设备价格","fee2":"套餐价格"}
	 * @param wb
	 *            excel转换的HSSFWorkbook对象
	 * @param beanClass
	 *            需要转化的目标对象,如DeviceInfo.class;支持BigDecimal,Integer,Long,Boolean,Date,String类型转换
	 * @return
	 * @throws Exception
	 */
	/*public <T> List<T> getExcelValue(Map<String, String> tableMap, Workbook wb, Class<T> beanClass) throws Exception {
		Set<Entry<String, String>> tableSet = tableMap.entrySet();
		Iterator<Entry<String, String>> tableIterator = tableSet.iterator();
		List<String> tableHead = new ArrayList<>();
		List<String> properties = new ArrayList<>();
		Entry<String, String> entry = null;
		while (tableIterator.hasNext()) {
			entry = tableIterator.next();
			if (entry != null) {
				tableHead.add(entry.getValue());
				properties.add(entry.getKey());
			}
		}
		// 返回数据集合
		List<T> list = new ArrayList<T>();
		// 对象字段名称
		String fieldname = "";
		// 对象方法名称
		String methodname = "";

		Method method = null;

		Sheet sheet = wb.getSheetAt(0);
		Row titleRow = sheet.getRow(0);
		int rowSize = sheet.getLastRowNum() + 1;
		int colSize = titleRow.getLastCellNum();
		List<String> tableHeadList = gettableHeadList(titleRow, colSize, tableHead);
		List<Integer> tableHeadIndexList = new ArrayList<>();
		List<Integer> tableIndex = new ArrayList<>();
		for (int i = 0; i < tableHead.size(); i++) {
			for (int j = 0; j < tableHeadList.size(); j++) {
				if (tableHeadList.get(j).equals(tableHead.get(i))) {
					tableHeadIndexList.add(j);
					tableIndex.add(j);
					break;
				}
			}
		}
		// 获取用户所用到的对象字段
		List<String> newProperties = getExistProperties(properties, tableHeadIndexList);

		list = new ArrayList<T>();
		// 得到对象所有字段
		Field fields[] = beanClass.getDeclaredFields();

		// 创建一个行对象
		Row row = null;
		for (int m = 1; m < rowSize; m++) {
			row = sheet.getRow(m);
			if (row == null) {
				continue;
			}
			// 创建一个泛型类型实例
			T t = beanClass.newInstance();
			// 排除掉表格中整行都是空值或空串
			int count = getNullCounts(colSize, row, tableIndex.size());
			if (count >= tableIndex.size()) {
				continue;
			}

			for (int i = 0; i < colSize; i++) {
				for (int k = 0; k < newProperties.size(); k++) {
					if (tableIndex.get(k) != null && row.getCell(tableIndex.get(k)) != null
							&& row.getCell(tableIndex.get(k)).getRichStringCellValue() != null && StringUtil
									.isNotEmpty(row.getCell(tableIndex.get(k)).getRichStringCellValue().toString())) {
						for (int j = 0; j < fields.length; j++) {
							fieldname = fields[j].getName();
							if (fields[j].getName().equals(newProperties.get(k))) {
								// 拼接set方法
								methodname = "set" + StringUtil.capitalize(fieldname);
								// 赋值给字段
								method = beanClass.getDeclaredMethod(methodname, fields[j].getType());
								if (fields[j].getType() == BigDecimal.class) {
									method.invoke(t, new BigDecimal(
											row.getCell(tableIndex.get(k)).getRichStringCellValue().toString()));
								} else if (fields[j].getType() == Integer.class) {
									method.invoke(t, Integer.parseInt(
											row.getCell(tableIndex.get(k)).getRichStringCellValue().toString()));
								} else if (fields[j].getType() == Long.class) {
									method.invoke(t, Long.parseLong(
											row.getCell(tableIndex.get(k)).getRichStringCellValue().toString()));
								} else if (fields[j].getType() == Boolean.class) {
									method.invoke(t, Boolean.parseBoolean(
											row.getCell(tableIndex.get(k)).getRichStringCellValue().toString()));
								} else if (fields[j].getType() == Date.class) {
									String time = row.getCell(tableIndex.get(k)).getRichStringCellValue().toString();
									Calendar c = new GregorianCalendar(1900, 0, -1);
									Date d = c.getTime();
									time = DateUtil.formatDateToString(DateUtils.addDays(d, Integer.parseInt(time)),
											"yyyy-MM-dd");
									method.invoke(t, time);
								} else {
									method.invoke(t,
											row.getCell(tableIndex.get(k)).getRichStringCellValue().toString());
								}
								break;
							}
						}
					}
				}
			}
			list.add(t);
		}
		return list;
	}*/
	public <T> List<T> getExcelValue(Map<String, String> tableMap, Workbook wb, Class<T> beanClass) throws Exception {
		Set<Entry<String, String>> tableSet = tableMap.entrySet();
		Iterator<Entry<String, String>> tableIterator = tableSet.iterator();
		List<String> tableHead = new ArrayList<>();
		List<String> properties = new ArrayList<>();
		Entry<String, String> entry = null;
		while (tableIterator.hasNext()) {
			entry = tableIterator.next();
			if (entry != null) {
				tableHead.add(entry.getValue());
				properties.add(entry.getKey());
			}
		}
		// 返回数据集合
		List<T> list = new ArrayList<T>();
		// 对象字段名称
		String fieldname = "";
		// 对象方法名称
		String methodname = "";

		Method method = null;

		Sheet sheet = wb.getSheetAt(0);
		Row titleRow = sheet.getRow(0);
		int rowSize = sheet.getLastRowNum() + 1;
		int colSize = titleRow.getLastCellNum();
		List<String> tableHeadList = gettableHeadList(titleRow, colSize, tableHead);
		List<Integer> tableHeadIndexList = new ArrayList<>();
		List<Integer> tableIndex = new ArrayList<>();
		for (int i = 0; i < tableHead.size(); i++) {
			for (int j = 0; j < tableHeadList.size(); j++) {
				if (tableHeadList.get(j).equals(tableHead.get(i))) {
					tableHeadIndexList.add(i);
					tableIndex.add(j);
					break;
				}
			}
		}
		// 获取用户所用到的对象字段
		List<String> newProperties = getExistProperties(properties, tableHeadIndexList);

		list = new ArrayList<T>();
		// 得到对象所有字段
		Field fields[] = beanClass.getDeclaredFields();

		// 创建一个行对象
		Row row = null;
		for (int m = 1; m < rowSize; m++) {
			row = sheet.getRow(m);
			if (row == null) {
				continue;
			}
			// 创建一个泛型类型实例
			T t = beanClass.newInstance();
			// 排除掉表格中整行都是空值或空串
			int count = getNullCounts(colSize, row, tableIndex.size());
			if (count >= tableIndex.size()) {
				continue;
			}

			for (int i = 0; i < colSize; i++) {
				for (int k = 0; k < newProperties.size(); k++) {
					if (tableIndex.get(k) != null && row.getCell(tableIndex.get(k)) != null
							&& row.getCell(tableIndex.get(k)).getRichStringCellValue() != null && StringUtil
									.isNotEmpty(row.getCell(tableIndex.get(k)).getRichStringCellValue().toString())) {
						for (int j = 0; j < fields.length; j++) {
							fieldname = fields[j].getName();
							if (fields[j].getName().equals(newProperties.get(k))) {
								// 拼接set方法
								methodname = "set" + StringUtil.capitalize(fieldname);
								// 赋值给字段
								method = beanClass.getDeclaredMethod(methodname, fields[j].getType());
								if (fields[j].getType() == BigDecimal.class) {
									method.invoke(t, new BigDecimal(
											row.getCell(tableIndex.get(k)).getRichStringCellValue().toString()));
								} else if (fields[j].getType() == Integer.class) {
									method.invoke(t, Integer.parseInt(
											row.getCell(tableIndex.get(k)).getRichStringCellValue().toString()));
								} else if (fields[j].getType() == Long.class) {
									method.invoke(t, Long.parseLong(
											row.getCell(tableIndex.get(k)).getRichStringCellValue().toString()));
								} else if (fields[j].getType() == Boolean.class) {
									method.invoke(t, Boolean.parseBoolean(
											row.getCell(tableIndex.get(k)).getRichStringCellValue().toString()));
								} else if (fields[j].getType() == Date.class) {
									String time = row.getCell(tableIndex.get(k)).getRichStringCellValue().toString();
									Calendar c = new GregorianCalendar(1900, 0, -1);
									Date d = c.getTime();
									time = DateUtil.formatDateToString(DateUtils.addDays(d, Integer.parseInt(time)),
											"yyyy-MM-dd");
									method.invoke(t, time);
								} else {
									method.invoke(t,
											row.getCell(tableIndex.get(k)).getRichStringCellValue().toString());
								}
								break;
							}
						}
					}
				}
			}
			list.add(t);
		}
		return list;
	}

	/**
	 * <p>
	 * Title: getNullCounts
	 * </p>
	 * <p>
	 * 功能描述:获取excel当前行为空的字段数量
	 * </p>
	 * 
	 * @param colSize
	 * @param row
	 * @param tableIndex
	 * @return
	 */
	private static int getNullCounts(int colSize, Row row, int tableSize) {
		if (row == null) {
			return 0;
		}
		int count = 0;
		for (int i = 0; i < colSize; i++) {
			Cell cell = row.getCell(i);
			if (cell == null) {
				count++;
				continue;
			}
			row.getCell(i).setCellType(CellType.STRING);
			if (i <= tableSize) {
				if (row.getCell(i).getRichStringCellValue() == null
						|| !StringUtil.isNotEmpty(row.getCell(i).getRichStringCellValue().toString().trim())) {
					count++;
				}
			}
		}
		return count;
	}

	/**
	 * <p>
	 * Title: getExistProperties
	 * </p>
	 * <p>
	 * 功能描述:获取用户所用到的字段
	 * </p>
	 * 
	 * @param properties
	 * @param tableHeadIndexList
	 * @return
	 */
	private List<String> getExistProperties(List<String> properties, List<Integer> tableHeadIndexList) {
		List<String> newProperties = new ArrayList<>();
		for (int i = 0; i < properties.size(); i++) {
			for (Integer tableHeadIndex : tableHeadIndexList) {
				if (i == tableHeadIndex) {
					newProperties.add(properties.get(i));
				}
			}
		}
		return newProperties;
	}

	/**
	 * <p>
	 * Title: gettableHeadList
	 * </p>
	 * <p>
	 * 功能描述:获取用户所用到的表头
	 * </p>
	 * 
	 * @param titleRow
	 * @param colSize
	 * @param tableHead
	 * @return
	 */
	private List<String> gettableHeadList(Row titleRow, int colSize, List<String> tableHead) {
		List<String> indexList = new ArrayList<>();
		for (int i = 0; i < colSize; i++) {
			titleRow.getCell(i).setCellType(CellType.STRING);
			for (int j = 0; j < tableHead.size(); j++) {
				if (titleRow.getCell(i).getRichStringCellValue() != null
						&& StringUtil.isNotEmpty(titleRow.getCell(i).getRichStringCellValue().toString())
						&& tableHead.get(j).equals(titleRow.getCell(i).getRichStringCellValue().toString())) {
					indexList.add(tableHead.get(j));
				}
			}
		}
		return indexList;
	}

	/**
	 * <p>
	 * Title: checkExcel
	 * </p>
	 * <p>
	 * 功能描述:校验excel合法性和是否有重复
	 * </p>
	 * 
	 * @param wb
	 * @param tableHead
	 * @param repeatFeild
	 * @return
	 */
	public static String checkExcel(HSSFWorkbook wb, String[] tableHead, String[] repeatFeild, boolean isCheckNull) {
		// 校验合法性
		String check = ExcelUtil.checkExcel(wb, tableHead, isCheckNull);
		if (!"true".equals(check)) {
			return check;
		}
		// 校验excel表内数据是否有重复
		String isRepeat = ExcelUtil.checkRepeat(wb, repeatFeild);
		if (!"true".equals(isRepeat)) {
			return isRepeat;
		}
		return "true";
	}
}
